﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace PromotionApp.Utility
{
    public class SQLHelper
    {
        #region Private Variable

        private string _ConnectionString = string.Empty;

        #endregion

        #region Publish Method

        public SqlConnection GetConnection()
        {
            return new SqlConnection(this._ConnectionString);
        }

        public void ExecuteNonQuery(string strSql, List<SqlParameter> paramList, CommandType commandType)
        {
            using (SqlConnection conn = new SqlConnection(_ConnectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;
                    comm.Connection.Open();
                    comm.CommandType = commandType;
                    comm.CommandText = strSql;

                    foreach (SqlParameter param in paramList)
                    {
                        comm.Parameters.Add(param);
                    }

                    int rowEffected = comm.ExecuteNonQuery();
                    comm.Connection.Close();
                }
            }
        }

        public void ExecuteNonQueryTransaction(string strSql, List<SqlParameter> paramList, CommandType commandType, SqlConnection conn)
        {
            using (SqlCommand comm = new SqlCommand())
            {
                comm.Connection = conn;                
                comm.CommandType = commandType;
                comm.CommandText = strSql;

                foreach (SqlParameter param in paramList)
                {
                    comm.Parameters.Add(param);
                }

                int rowEffected = comm.ExecuteNonQuery();                
            }
        }

        public object ExecuteScalar(string strSql, List<SqlParameter> paramList, CommandType commandType)
        {
            object result = null;

            using (SqlConnection conn = new SqlConnection(_ConnectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;
                    comm.Connection.Open();
                    comm.CommandType = commandType;
                    comm.CommandText = strSql;

                    foreach (SqlParameter param in paramList)
                    {
                        comm.Parameters.Add(param);
                    }

                    result = comm.ExecuteScalar();
                    comm.Connection.Close();
                }
            }

            return result;
        }

        public DataSet GetDataSet(string strSql, List<SqlParameter> paramList, CommandType commandType)
        {
            DataSet ds = new DataSet();

            using (SqlConnection conn = new SqlConnection(_ConnectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    comm.Connection = conn;
                    comm.CommandType = commandType;
                    comm.CommandText = strSql;

                    foreach (SqlParameter param in paramList)
                    {
                        comm.Parameters.Add(param);
                    }

                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        da.SelectCommand = comm;
                        da.Fill(ds);
                    }
                }
            }

            return ds;
        }

        #endregion

        #region Constructor

        public SQLHelper()
        {

        }

        public SQLHelper(string strConnectionString)
        {
            this._ConnectionString = strConnectionString;
        }

        #endregion
    }
}